package com.saic.parse.impl;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

import com.saic.parse.IImport;


public class ImportSubBrand implements IImport {

	@Override
	public void importExcel(Connection conn) throws Exception {
		
		Statement stmt = conn.createStatement();
		stmt.execute("delete from t_subbrand");
		InputStream stream = new FileInputStream("d:/subbrand.xls");  
		HSSFWorkbook wb = new HSSFWorkbook(stream);
		Sheet sheet = wb.getSheetAt(0);  
		for(Row row:sheet){
			String autohomeid = null;			// 汽车之家主品牌id
			String name = null;					// 主品牌名
			String subbrandWebId = null;		// 汽车之家子品牌 webid
			String subbrandname = null;			// 子品牌名
			String desc = null;					// 描述
			String xcarid = null;				// xcar子品牌id
			String bitautoid = null;			// bitauto子品牌webid
			String url = null;
			for(Cell cell:row){
				//主品牌web_id
				if(cell.getColumnIndex()==0){
					autohomeid = cell.toString();
				}
				//主品牌name
				if(cell.getColumnIndex()==1){
					name = cell.toString();
				}
				//子品牌webid
				if(cell.getColumnIndex()==2){
					subbrandWebId = cell.toString();
				}
				//子品牌name
				if(cell.getColumnIndex()==3){
					subbrandname = cell.toString();
				}
				//bitautoid
				if(cell.getColumnIndex()==5){
					bitautoid = cell.toString();
				}
				//url
				if(cell.getColumnIndex()==7){
					xcarid = cell.toString();
				}
			}
			ResultSet resultSet = stmt.executeQuery("select id from t_brand as brand where brand.autoHomeID='" + autohomeid+"'");
			int brandId=0;
			while(resultSet.next())
				 brandId = new Integer(resultSet.getString(1));				//关联的主品牌id
			PreparedStatement pstate = conn.prepareStatement("insert into t_subbrand (name,`desc`,xcardid,autoHomeId,bitautoId,brandId,status) values(?,?,?,?,?,?,0)");
			pstate.setString(1, subbrandname);
			pstate.setString(2, subbrandname);
			pstate.setString(3, xcarid);
			pstate.setString(4, subbrandWebId);
			pstate.setString(5, bitautoid);
			pstate.setInt(6, brandId);
			pstate.execute();
		}
		conn.commit();
			
	}
}
